<?php
	// 输出Excel文件头，可把user.csv换成你要的文件名 
	$sjj1=$_POST["sjj1"];//大于等于这个时间截
	$sjj2=$_POST["sjj2"];//小于等于这个时间截
	$GongDan=$_POST["GongDan"];//工單號
	//header('Content-Type: application/vnd.ms-excel'); 
	//header('Content-Disposition: attachment;filename="kaoxiangbaobiao.csv"'); 
	//header('Cache-Control: max-age=0'); 
	header('Content-Type:application/vnd.ms-excel;charset=utf-8');
	
	header('Content-Disposition: attachment;filename="烤箱报表.csv"'); 
	header('Cache-Control: max-age=0');
	
	$pdo =new pdo("mysql:host=172.31.3.21;port=3306;dbname=tddb;","root","tdled2018");//PDO连接MySQL
	$pdo->query("set character set 'utf8'");//读库
	$pdo->query("set names 'utf8'");//写库

	if($sjj1!=="" and $sjj2!=="" and $GongDan!=="")
	{
		$sql = "SELECT
			 GongXu AS 工序
			,h.KaoXiangHao AS 烤箱号
			,KXMen AS 烤箱门
			,GongDanHao AS 工单号
			,PCS AS '数量(片)'
			,InGongHao AS 进烤人工号
			,p1.Name AS 进烤人姓名
			,InTime AS 进烤时间
			,OutGongHao AS 出烤人工号
			,p2.Name AS 出烤人姓名
			,OutTime AS 出烤时间
		FROM
			kaoxiang_h AS h
		INNER JOIN
			kaoxiang_d AS d
		ON
			h.KXHID = d.KXHID
		LEFT JOIN
			person AS p1
		ON
			InGongHao = p1.QR
		LEFT JOIN
			person AS p2
		ON
			OutGongHao = p2.QR
		LEFT JOIN
			kaoxiang_tab AS t
		ON
			t.KaoXiangHao = h.KaoXiangHao
		WHERE
			DATE(InTime) between '".$sjj1."' and '".$sjj2."' and GongDanHao LIKE'".$GongDan."%'";
	}
	elseif($sjj1!=="" and $sjj2!=="" and $GongDan==="")
	{
		$sql = "SELECT
			 GongXu AS 工序
			,h.KaoXiangHao AS 烤箱号
			,KXMen AS 烤箱门
			,GongDanHao AS 工单号
			,PCS AS '数量(片)'
			,InGongHao AS 进烤人工号
			,p1.Name AS 进烤人姓名
			,InTime AS 进烤时间
			,OutGongHao AS 出烤人工号
			,p2.Name AS 出烤人姓名
			,OutTime AS 出烤时间
		FROM
			kaoxiang_h AS h
		INNER JOIN
			kaoxiang_d AS d
		ON
			h.KXHID = d.KXHID
		LEFT JOIN
			person AS p1
		ON
			InGongHao = p1.QR
		LEFT JOIN
			person AS p2
		ON
			OutGongHao = p2.QR
		LEFT JOIN
			kaoxiang_tab AS t
		ON
			t.KaoXiangHao = h.KaoXiangHao
		WHERE
			DATE(InTime) between '".$sjj1."' and '".$sjj2."'";
	}
	elseif($sjj1==="" and $sjj2==="" and $GongDan!=="")
	{
		$sql = "SELECT
			 GongXu AS 工序
			,h.KaoXiangHao AS 烤箱号
			,KXMen AS 烤箱门
			,GongDanHao AS 工单号
			,PCS AS '数量(片)'
			,InGongHao AS 进烤人工号
			,p1.Name AS 进烤人姓名
			,InTime AS 进烤时间
			,OutGongHao AS 出烤人工号
			,p2.Name AS 出烤人姓名
			,OutTime AS 出烤时间
		FROM
			kaoxiang_h AS h
		INNER JOIN
			kaoxiang_d AS d
		ON
			h.KXHID = d.KXHID
		LEFT JOIN
			person AS p1
		ON
			InGongHao = p1.QR
		LEFT JOIN
			person AS p2
		ON
			OutGongHao = p2.QR
		LEFT JOIN
			kaoxiang_tab AS t
		ON
			t.KaoXiangHao = h.KaoXiangHao
		WHERE
			GongDanHao LIKE'".$GongDan."%'";
	};


	$res=$pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));//准备查询语句
	$res->execute(); //执行一条查询语句
	//$KX=$res->fetchAll();//取出所有行

	$fp = fopen('php://output', 'a'); 
	// 输出Excel列名信息 
	$head = array('工序','烤箱号', '烤箱门', '工单号','数量(K)', '进烤人工号', '进烤人姓名', '进烤时间', '出烤人工号', '出烤人姓名','出烤时间'); 
	foreach ($head as $i => $v) 
	{
		$head[$i] = iconv('utf-8', 'gbk', $v); 
	}
	// 将数据通过fputcsv写到文件句柄 
	fputcsv($fp, $head);
	//
	// 计数器 
	$cnt = 0; 
	// 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小 
	$limit = 100000; 
	// 逐行取出数据，不浪费内存 

	while ($row=$res->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT))
	{ 
		$data = $row[0] . "\t" .$row[1] . "\t" . $row[2] ."\t". $row[3] . "\t" .$row[4] . "\t" .$row[5] . "\t" .$row[6] . "\t" .$row[7] . "\t" .$row[8] . "\t" .$row[9]  ."\n";

		foreach ($row as $i => $v) 
		{ 
			$row[$i] = iconv("UTF-8", "GB2312//IGNORE",$v); 
		} 
		fputcsv($fp, $row); 
	}
	
	fclose($fp); //最后要关闭资源
?>
